package com.yenlien.traceyun.plant.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.entity.ColdDevData;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface ColdDevDataDao extends BaseJpaDao<ColdDevData> {

//    @Query(value = "SELECT * FROM t_cold_data WHERE box_id = ?1 GROUP BY dev_id, DATE_FORMAT(create_time,'%Y-%m-%d %H') ORDER BY create_time DESC", nativeQuery = true)
    //先排序在分组  最后接口安装冷库顺序升序排序 对冷库名进行处理然后排序    ORDER BY create_time DESC不能取消
    @Query(value = "SELECT ANY_VALUE(id) id, ANY_VALUE(box_id) boxId, dev_id, ANY_VALUE(t.`value`) `value`, ANY_VALUE(t.`name`) `name`, ANY_VALUE(t.create_time) `createTime` FROM " +
            "(SELECT * FROM t_cold_data WHERE box_id = ?1 HAVING 1 ORDER BY create_time DESC) t GROUP BY t.dev_id "
//            + " ORDER BY CONVERT(REPLACE(REPLACE(ANY_VALUE(t.`name`), '库内温度1', ''), '冷库', ''), signed)" 这种排序针对单个盒子，多个盒子则不行
            , nativeQuery = true)
    List<JSONObject> getByBoxId(String boxId);


    @Query(value = "SELECT ANY_VALUE(t.`value`) `value`, ANY_VALUE(t.`name`) `name` FROM " +
            "(SELECT * FROM t_cold_data HAVING 1 ORDER BY create_time DESC) t GROUP BY t.dev_id", nativeQuery = true)
//    @Query(value = "SELECT ANY_VALUE(t.`value`) `value`, ANY_VALUE(t.`name`) `name` FROM " +
//            "(SELECT `value`, `name`, dev_id, box_id FROM t_cold_data HAVING 1 ORDER BY create_time DESC) t GROUP BY CONCAT(t.dev_id, t.box_id)", nativeQuery = true)
    List<JSONObject> getGroupDevId();


    @Query(value = "SELECT * FROM t_cold_data WHERE box_id = ?1 AND dev_id = ?2 ORDER BY create_time DESC LIMIT 10", nativeQuery = true)
    List<ColdDevData> getTByBDid(String boxId, String devId);

    @Query(value = "SELECT `value` FROM t_cold_data WHERE box_id = ?1 AND dev_id = ?2 ORDER BY create_time DESC LIMIT 1", nativeQuery = true)
    String getByBDid(String boxId, String devId);
}
